﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Collections;
using Microsoft.ApplicationBlocks.Data;
using System.IO;
namespace kxjsj
{
    public partial class expert : Form
    {
        public expert()
        {
            InitializeComponent();
        }

        private void 导入专家ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string Access =System.Windows .Forms .Application.StartupPath + "\\data\\kxjsj.mdb";
            string Excel = System.Windows.Forms.Application.StartupPath + "\\data\\Book1.xls";
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel文件|*.xls";
            if (ofd.ShowDialog() != DialogResult.Cancel)
            {
                Excel = ofd.FileName;
            }
            try
            {
                string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel + ";Extended Properties=Excel 8.0;";
                using (OleDbConnection conn = new OleDbConnection(connect))
                {
                    using (OleDbCommand cmd = new OleDbCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = "INSERT INTO [MS Access;Database=" + Access + "].[expert] SELECT * FROM [Sheet1$]";
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
                MessageBox.Show("导入成功！", "提示");
                this.expertTableAdapter.Fill(this.ds_kxjsj.expert);
           }
            catch (Exception ex)
            {
                MessageBox.Show("Excel文件格式错误！请打开空Excel表对照格式。\r\n"+ex.Message , "错误提示");
            }
        }

        private void expert_Load(object sender, EventArgs e)
        {
            // TODO: 这行代码将数据加载到表“ds_kxjsj.expert”中。您可以根据需要移动或移除它。
            this.expertTableAdapter.Fill(this.ds_kxjsj.expert);

        }

        private void 刷新ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.expertTableAdapter.Fill(this.ds_kxjsj.expert);
        }

        private void 格式说明ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start(System.Windows.Forms.Application.StartupPath + "\\data\\Book1.xls");
        }

        private void 清空专家表ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string Access = System.Windows.Forms.Application.StartupPath + "\\data\\kxjsj.mdb";
            string Excel = System.Windows.Forms.Application.StartupPath + "\\data\\Book1.xls";
            string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel + ";Extended Properties=Excel 8.0;";
            using (OleDbConnection conn = new OleDbConnection(connect))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "delete from [MS Access;Database=" + Access + "].[expert] ";
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            MessageBox.Show("清空成功！", "提示");
            this.expertTableAdapter.Fill(this.ds_kxjsj.expert);
        }

        private void 查询是否为完成人ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string Access = System.Windows.Forms.Application.StartupPath + "\\data\\kxjsj.mdb";
            //遍历expert表，获得所有的姓名
            string exp_name = "";
            string exl_file = System.Windows.Forms.Application.StartupPath + "\\tmp\\检查结果" + DateTime.Now.ToFileTime().ToString() + ".xls";
            File.Copy(System.Windows.Forms.Application.StartupPath + "\\data\\检查结果.xls", exl_file, true);
            Stream myStream;
            myStream = File.OpenWrite(exl_file);
            string columnTitle = "专家姓名\t专家单位\t专家手机\t完成人姓名\t完成人单位\t完成人手机\t项目名称\t项目编号";
            string columnValue = "";
            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
            sw.WriteLine(columnTitle);
            using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Access))
            {
                OleDbDataReader exp = SqlHelper.ExecuteReader(conn, CommandType.Text, "select * from expert");
                //用每个姓名去查author表
                while (exp.Read())
                {
                    exp_name = exp["name"].ToString().Trim().Replace(" ", "").Replace("　", "");
                    OleDbDataReader auth = SqlHelper.ExecuteReader(conn, CommandType.Text, "select * from author where name='" + exp_name + "';");
                    while (auth.Read())
                    {
                        OleDbDataReader pro = SqlHelper.ExecuteReader(conn, CommandType.Text, "select * from project where inner_id='" + auth["p_inner_id"].ToString() + "'");
                        pro.Read();
                        //把找到的同名的人的信息写入一张excel表格，专家姓名，专家单位，专家手机，完成人姓名，完成人单位，完成人手机
                        columnValue = exp["name"].ToString() + "\t" + exp["unit"].ToString() + "\t" + exp["mobile"].ToString() + "\t" +
                            auth["name"].ToString() + "\t" + auth["unit"].ToString() + "\t" + auth["mobile"].ToString()
                            + "\t" + pro["name"].ToString() + "\t" + pro["inner_id"].ToString();
                        sw.WriteLine(columnValue);
                    }
                }
            }
            sw.Close();
            myStream.Close();
            System.Diagnostics.Process.Start(exl_file);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string Access = System.Windows.Forms.Application.StartupPath + "\\data\\kxjsj.mdb";
            //遍历expert表，获得所有的姓名
            string exp_name = "";
            string exl_file = System.Windows.Forms.Application.StartupPath + "\\tmp\\检查结果" + DateTime.Now.ToFileTime().ToString() + ".xls";
            File.Copy(System.Windows.Forms.Application.StartupPath + "\\data\\检查结果.xls", exl_file, true);
            Stream myStream;
            myStream = File.OpenWrite(exl_file);
            string columnTitle = "专家姓名\t专家单位\t专家手机\t完成人姓名\t完成人单位\t完成人手机\t项目名称\t项目编号";
            string columnValue = "";
            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
            sw.WriteLine(columnTitle);
            using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Access))
            {
                exp_name = textBox1.Text .Trim().Replace(" ", "").Replace("　", "");
                OleDbDataReader auth = SqlHelper.ExecuteReader(conn, CommandType.Text, "select * from author where name='" + exp_name + "';");
                while (auth.Read())
                {
                    OleDbDataReader pro = SqlHelper.ExecuteReader(conn, CommandType.Text, "select * from project where inner_id='" + auth["p_inner_id"].ToString() + "'");
                    pro.Read();
                    //把找到的同名的人的信息写入一张excel表格，专家姓名，专家单位，专家手机，完成人姓名，完成人单位，完成人手机
                    columnValue = exp_name + "\t" + "" + "\t" + "" + "\t" +
                        auth["name"].ToString() + "\t" + auth["unit"].ToString() + "\t" + auth["mobile"].ToString()
                        + "\t" + pro["name"].ToString() + "\t" + pro["inner_id"].ToString();
                    sw.WriteLine(columnValue);
                }
            }
            sw.Close();
            myStream.Close();
            System.Diagnostics.Process.Start(exl_file);
        }

        private void 单位ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string Access = System.Windows.Forms.Application.StartupPath + "\\data\\kxjsj.mdb";
            //遍历expert表，获得所有的单位
            string unit_name = "";
            string exl_file = System.Windows.Forms.Application.StartupPath + "\\tmp\\检查结果" + DateTime.Now.ToFileTime().ToString() + ".xls";
            File.Copy(System.Windows.Forms.Application.StartupPath + "\\data\\检查结果.xls", exl_file, true);
            Stream myStream;
            myStream = File.OpenWrite(exl_file);
            string columnTitle = "专家姓名\t专家单位\t专家手机\t完成人姓名\t完成人单位\t完成人手机\t项目名称\t项目编号";
            string columnValue = "";
            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
            sw.WriteLine(columnTitle);
            using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Access))
            {
                OleDbDataReader exp = SqlHelper.ExecuteReader(conn, CommandType.Text, "select * from expert");
                //用每个姓名去查author表
                while (exp.Read())
                {
                    unit_name = exp["unit"].ToString().Trim().Replace(" ", "").Replace("　", "");
                    OleDbDataReader auth = SqlHelper.ExecuteReader(conn, CommandType.Text, "select * from author where unit='" + unit_name + "';");
                    while (auth.Read())
                    {
                        OleDbDataReader pro = SqlHelper.ExecuteReader(conn, CommandType.Text, "select * from project where inner_id='" + auth["p_inner_id"].ToString() + "'");
                        pro.Read();
                        //把找到的同名的人的信息写入一张excel表格，专家姓名，专家单位，专家手机，完成人姓名，完成人单位，完成人手机
                        columnValue = exp["name"].ToString() + "\t" + exp["unit"].ToString() + "\t" + exp["mobile"].ToString() + "\t" +
                            auth["name"].ToString() + "\t" + auth["unit"].ToString() + "\t" + auth["mobile"].ToString()
                            + "\t" + pro["name"].ToString() + "\t" + pro["inner_id"].ToString();
                        sw.WriteLine(columnValue);
                    }
                }
            }
            sw.Close();
            myStream.Close();
            System.Diagnostics.Process.Start(exl_file);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string Access = System.Windows.Forms.Application.StartupPath + "\\data\\kxjsj.mdb";
            //遍历expert表，获得所有的单位
            string unit_name = "";
            string exl_file = System.Windows.Forms.Application.StartupPath + "\\tmp\\检查结果" + DateTime.Now.ToFileTime().ToString() + ".xls";
            File.Copy(System.Windows.Forms.Application.StartupPath + "\\data\\检查结果.xls", exl_file, true);
            Stream myStream;
            myStream = File.OpenWrite(exl_file);
            string columnTitle = "专家姓名\t专家单位\t专家手机\t完成人姓名\t完成人单位\t完成人手机\t项目名称\t项目编号";
            string columnValue = "";
            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
            sw.WriteLine(columnTitle);
            using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Access))
            {

                unit_name = textBox2.Text .Trim().Replace(" ", "").Replace("　", "");
                OleDbDataReader auth = SqlHelper.ExecuteReader(conn, CommandType.Text, "select * from author where unit like '%" + unit_name + "%';");
                while (auth.Read())
                {
                    OleDbDataReader pro = SqlHelper.ExecuteReader(conn, CommandType.Text, "select * from project where inner_id='" + auth["p_inner_id"].ToString() + "'");
                    pro.Read();
                    //把找到的同名的人的信息写入一张excel表格，专家姓名，专家单位，专家手机，完成人姓名，完成人单位，完成人手机
                    columnValue =  ""+ "\t" + unit_name + "\t" + "" + "\t" +
                        auth["name"].ToString() + "\t" + auth["unit"].ToString() + "\t" + auth["mobile"].ToString()
                        + "\t" + pro["name"].ToString() + "\t" + pro["inner_id"].ToString();
                    sw.WriteLine(columnValue);
                }
            }
            sw.Close();
            myStream.Close();
            System.Diagnostics.Process.Start(exl_file);
        }
    }
}
